'use strict'
const code = require('../errorCode.js')
const Service = require('egg').Service
const moment = require('moment');

class Mysql extends Service {
  async findAll(db,name,where={},columns="*") {
    const { ctx, app } = this;
    const sql = `select ${
      Array.isArray(columns) ? columns.join(",") : "*"
    } from ${name} ${ctx.whereStr(where)}`;
    console.log('查询语句FindAll:',sql)
    let result = {}
    try {
      const conn = await app.mysql.get(db);
      const rows = await conn.query(sql);
      const total = await conn.query(
        `select count(1) as total from ${name} ${ctx.whereStr(where)}`
      );
      result = app.list(rows, total[0].total);
    } catch (e) {
      this.logger.error("执行sql语句发生错误", sql);
      result = app.error(code.SQL, "执行sql语句发生错误"+sql+' '+e.message);
    }
    return result;
  }

  async find(db,name,where={},columns="*",orders=this.app.config.mysql.defaultOrders,offset=0,limit=this.app.config.mysql.defaultLimit) {
    const { ctx, app } = this;
    const sql = `select ${
      Array.isArray(columns) ? columns.join(",") : "*"
    } from ${name} ${ctx.whereStr(where)} order by ${orders.length>0 ? ctx.ordersArr(orders).join(
      ","
    ) : 'id asc'} limit ${offset}, ${limit}`;
    console.log('查询语句Find:',sql)
    let result = {}
    try {
      const conn = await app.mysql.get(db);
      const rows = await conn.query(sql);
      const total = await conn.query(
        `select count(1) as total from ${name} ${ctx.whereStr(where)}`
      );
      result = app.list(rows, total[0].total);
    } catch (e) {
      this.logger.error("执行sql语句发生错误", sql);
      result = app.error(code.SQL, "执行sql语句发生错误"+sql+' '+e.message);
    }
    return result;
  }

  async findOne(db,name,where={},columns="*") {
    const { ctx, app } = this;
    const sql = `select ${
      Array.isArray(columns) ? columns.join(",") : "*"
    } from ${name} ${ctx.whereStr(where)} order by id`;
    let result = {}
    console.log('查询语句FindOne:',sql)
    try {
      const conn = await app.mysql.get(db);
      const rows = await conn.query(sql);
      result = app.done(rows.length > 0 ? rows[0] : {});
    } catch (e) {
      this.logger.error("执行sql语句发生错误", sql);
      result = app.error(code.SQL, "执行sql语句发生错误! "+sql);
    }
    return result;
  }

  async findById(db,name,id,columns) {
    const { ctx, app } = this;
    let result = {}
    try {
      const conn = await this.app.mysql.get(db);
      const rows = await conn.get(name, { id });
      if (columns!='*'){
        if (Array.isArray(columns)){
          for(let i = 0; i < columns.length;i++){
            delete rows[columns[i]]
          }
        }
      }
      result = app.done(rows);
    } catch (e) {
      this.logger.error(
        "执行sql语句发生错误",
        `${db}:select ${columns} from ${name} where id='${id}'`
      );
      result = app.error(code.SQL, "执行sql语句发生错误");
    }
    return result;
  }

  //TODO: 要拆开成insert/update方便权限控制，
  async add(db,name,body,user) {
    const { ctx, app } = this;
    const time = moment(new Date).format('YYYY-MM-DD HH:mm:ss');
    const conn = await app.mysql.get(db);
    const tran = await conn.beginTransaction();
    let result = {}
    try {
      let data = {...ctx.request.body}
      delete data['id']
      result = app.done(await tran.insert(name, {...data,create_time:time,edit_time:time,creator:user.id,editor:user.id,create_organ:user.organ,edit_organ:user.organ}));
      await tran.commit();
    } catch (e) {
      await tran.rollback();
      app.logger.error(`${db}.${name} 插入失败 ${e.message}`);
      result = app.error(code.INSERT, `${db}.${name} 插入失败`);
    }
    return result;
  }

  async edit(db,name,body,user) {
    const { ctx, app } = this;
    const id = body.id || "";
    const time = moment(new Date).format('YYYY-MM-DD HH:mm:ss');
    const conn = await app.mysql.get(db);
    const tran = await conn.beginTransaction();
    let result = {}
    try {
      if (id != "") {
        result = app.done(await tran.update(name, {...ctx.request.body,edit_time:time,editor:user.id,edit_organ:user.organ}));
        await tran.commit();
      }else{
        await tran.rollback();
        app.logger.error(`${db}.${name} 更新时发生错误，缺失ID`);
        result = app.error(code.UPDATE, `${db}.${name} 更新时发生错误，缺失ID`);
      }
    } catch (e) {
      await tran.rollback();
      app.logger.error(`${db}.${name} 更新时发生错误，缺失ID ${e.message}`);
      result = app.error(code.UPDATE, `${db}.${name} 更新时发生错误，缺失ID`);
    }
    return result;
  }

  async remove(db,name,id) {
    const { ctx, app } = this;
    let result = {}
    try {
      const conn = await this.app.mysql.get(db);
      result = app.done(await conn.delete(name, {id}));
    } catch (e) {
      app.logger.error(`${db}.${name} 删除失败 ${e.message}`);
      result = app.error(code.DELETE, `${db}.${name} 删除失败`);
    }
    return result;
  }

  /**
   * 执行mapper/*.json中的sql语句
   */
  async excute(db,name,body){
    const {ctx,app} = this;
    const conn = await app.mysql.get(db);
    const tran = await conn.beginTransaction();
    let res = {};
    let sqlStr = ''
    try {
      const json = app.loadJson(db);
      let {sqlStr,params} = ctx.generateSql(json[name].sql,body)
      if(sqlStr.indexOf('select')==0){
        if (sqlStr.indexOf(' where ')>=0){
          if(JSON.stringify(params)!='{}'){
            sqlStr += ' and ' + ctx.whereStr(params).replace('where ','')
          }
        }else{
          if(JSON.stringify(params)!='{}'){
            sqlStr += ' ' + ctx.whereStr(params)
          }
        }
        if (json[name].hasOwnProperty('groupBy')){
          if (Array.isArray(json[name].groupBy)){
            if (json[name].groupBy.length>0)
            sqlStr += ' group by ' + json[name].groupBy.join(',')
          }
        }
        if (json[name].hasOwnProperty('orderBy')){
          if (JSON.stringify(json[name].orderBy)!='{}'){
            let orders = []
            for(let key in json[name].orderBy){
              orders.push(`${key} ${json[name].orderBy[key]}`)
            }
            sqlStr += ' order by ' + orders.join(',')
          }
        }
      }
      console.log('执行语句Excute:',sqlStr)
      const result = await tran.query(sqlStr);
      await tran.commit();
      res = app.done(Array.isArray(result) ? {rows:result} : result)
    } catch (e) {
      await tran.rollback();
      app.logger.error(e.message);
      res = app.error(code.EXCUTE, "执行失败: "+sqlStr+" "+e.message);
    }
    return res;
  }

  /**
   * 根据条件参数返回树
   */
  async tree(db,name,field,body){
    const {app} = this;
    const nodeWhere = JSON.stringify(body)=='{}' ? {parent:null} : body
    let where = {...nodeWhere}
    delete where[field]
    delete where["parent"]
    let result = {}
    // try{
      const conn = await this.app.mysql.get(db);
      const rows = await conn.select(name,{"where":nodeWhere,orders:[[field,'asc']]})
      if(Array.isArray(rows)){
        let tree = []
        for(let i = 0; i < rows.length;i++){
          tree.push(await this.getChildren(conn,name,rows[i],field,where))
        }
        result = app.done({tree})
      }else{
        result = app.error(code.TREE_NOT_ARRAY,'树的返回结果不是数组！'+JSON.stringify(rows))
      }
    // } catch (e) {
    //   result = app.error(code.TREE,'获取树时发生未知错误！'+e.message)
    // }
    return result;
  }

  async getChildren(conn,name,row,field,where){
    const rows = await conn.select(name,{"where":{...where,parent:row[field]},orders:[[field,'asc']]})
    if (Array.isArray(rows)){
      if(rows.length>0){
        let child = []
        for(let i = 0; i < rows.length;i++){
          child.push(await this.getChildren(conn,name,rows[i],field,where))
        }
        return {...row,children:child}
      }else{
        return row
      }
    }{
      return row
    }
  }
}

module.exports = Mysql